SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO


CREATE PROCEDURE dbo.[DnnProduct_Journal_Get]
@PortalId int,
@CurrentUserId int,
@JournalId int
AS
SELECT     j.JournalId, j.JournalTypeId, j.Title, j.Summary, j.UserId, j.DateCreated, j.DateUpdated, j.PortalId,
			j.ProfileId, j.GroupId, j.ObjectKey, j.AccessKey,
			"JournalOwner" = '<entity><id>' + CAST(p.UserId as nvarchar(150)) + '</id><name><![CDATA[' + p.DisplayName + ']]></name></entity>',
			"JournalAuthor" = CASE WHEN ISNULL(a.UserId,-1) >0 THEN '<entity><id>' + CAST(a.UserId as nvarchar(150)) + '</id><name><![CDATA[' + a.DisplayName + ']]></name></entity>' ELSE '' END,
			"JournalOwnerId" = ISNULL(j.ProfileId,j.UserId),
			 jt.Icon, jt.JournalType,
			"Profile" = CASE WHEN j.ProfileId > 0 THEN '<entity><id>' + CAST(p.UserID as nvarchar(150)) + '</id><name><![CDATA[' + p.DisplayName + ']]></name><vanity></vanity></entity>' ELSE '' END,
			jd.JournalXML, ContentItemId, j.ItemData			
FROM       	dbo.[DnnProduct_Journal] AS j INNER JOIN
			dbo.[DnnProduct_Journal_Types] as jt ON jt.JournalTypeId = j.JournalTypeId LEFT OUTER JOIN
			dbo.[DnnProduct_Journal_Data] as jd on jd.JournalId = j.JournalId LEFT OUTER JOIN
            dbo.[DnnProduct_Users] AS p ON j.ProfileId = p.UserID LEFT OUTER JOIN
            dbo.[DnnProduct_Users] AS a ON j.UserId = a.UserID
WHERE j.JournalId = @JournalId
GO
